from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from sqlalchemy import func
from sqlalchemy import Numeric

db = SQLAlchemy()

class Employee(db.Model):
    """员工模型"""
    __tablename__ = 'employees'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, comment='姓名')
    email = db.Column(db.String(120), unique=True, nullable=False, comment='邮箱')
    position = db.Column(db.String(100), nullable=False, comment='职位')
    department = db.Column(db.String(100), nullable=False, comment='部门')
    hire_date = db.Column(db.Date, nullable=False, comment='入职日期')
    salary = db.Column(Numeric(10, 2), nullable=False, comment='月薪')
    avatar = db.Column(db.String(255), default='https://via.placeholder.com/150', comment='头像URL')
    job_description = db.Column(db.Text, comment='职位描述')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    
    # 关联关系
    attendance_records = db.relationship('AttendanceRecord', backref='employee', lazy=True, cascade='all, delete-orphan')
    payroll_records = db.relationship('PayrollRecord', backref='employee', lazy=True, cascade='all, delete-orphan')
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'name': self.name,
            'email': self.email,
            'position': self.position,
            'department': self.department,
            'hireDate': self.hire_date.strftime('%Y-%m-%d'),
            'salary': float(self.salary),
            'avatar': self.avatar,
            'jobDescription': self.job_description
        }
    
    def __repr__(self):
        return f'<Employee {self.name}>'

class AttendanceRecord(db.Model):
    """考勤记录模型"""
    __tablename__ = 'attendance_records'
    
    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'), nullable=False, comment='员工ID')
    date = db.Column(db.Date, nullable=False, comment='考勤日期')
    status = db.Column(db.Enum('Present', 'Absent', 'Late', name='attendance_status'), nullable=False, comment='考勤状态')
    clock_in = db.Column(db.Time, comment='签到时间')
    clock_out = db.Column(db.Time, comment='签退时间')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    
    # 唯一约束：每个员工每天只能有一条考勤记录
    __table_args__ = (db.UniqueConstraint('employee_id', 'date', name='unique_employee_date'),)
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'employeeId': self.employee_id,
            'date': self.date.strftime('%Y-%m-%d'),
            'status': self.status,
            'clockIn': self.clock_in.strftime('%H:%M') if self.clock_in else None,
            'clockOut': self.clock_out.strftime('%H:%M') if self.clock_out else None
        }
    
    def __repr__(self):
        return f'<AttendanceRecord {self.employee_id} {self.date}>'

class PayrollRecord(db.Model):
    """工资记录模型"""
    __tablename__ = 'payroll_records'
    
    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'), nullable=False, comment='员工ID')
    pay_period = db.Column(db.String(20), nullable=False, comment='薪资周期')
    base_salary = db.Column(Numeric(10, 2), nullable=False, comment='基本工资')
    bonus = db.Column(Numeric(10, 2), default=0, comment='奖金')
    deductions = db.Column(Numeric(10, 2), default=0, comment='扣款')
    net_pay = db.Column(Numeric(10, 2), nullable=False, comment='实发工资')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'employeeId': self.employee_id,
            'payPeriod': self.pay_period,
            'baseSalary': float(self.base_salary),
            'bonus': float(self.bonus),
            'deductions': float(self.deductions),
            'netPay': float(self.net_pay)
        }
    
    def __repr__(self):
        return f'<PayrollRecord {self.employee_id} {self.pay_period}>'

class User(db.Model):
    """用户模型（用于登录认证）"""
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False, comment='用户名')
    password_hash = db.Column(db.String(120), nullable=False, comment='密码哈希')
    is_active = db.Column(db.Boolean, default=True, comment='是否激活')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    
    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'username': self.username,
            'isActive': self.is_active
        }
    
    def __repr__(self):
        return f'<User {self.username}>'